package io.renren.dao;

import io.renren.common.dao.BaseDao;
import io.renren.dto.MarketDTO;
import io.renren.dto.MarketmxDTO;
import io.renren.entity.MarketmxEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * 市场监管明细表
 *
 * @author 罗胜平 781757616@qq.com
 * @since 1.0.0 2019-11-22
 */
@Mapper
public interface MarketmxDao extends BaseDao<MarketmxEntity> {

    @Select("select f.food_name,f.unit,mx.* from canteens_marketmx mx LEFT JOIN canteens_food f ON f.food_id=mx.food_id " +
            "WHERE mx.marketmx_id='${id}'")
    List<MarketmxDTO> selectDetail(long id);
    @Select("select f.food_name,f.unit,mx.* from canteens_marketmx mx LEFT JOIN canteens_food f ON f.food_id=mx.food_id " +
            "ORDER BY mx.operator_time desc")
    List<MarketmxDTO> slectPage();
    //查询当前提供商品的所有供应商（食材）
    @Select("select count(*)as totalNum,A.* from canteens_stock_ordermx A ,canteens_stock_order B where A.stock_order_id = B.stock_order_id and B.`status`>'4' and A.food_id = '${foodId}'  " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') < DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            "and  DATE_FORMAT('${endDate}','%Y-%m-%d') > DATE_FORMAT(B.create_date,'%Y-%m-%d')  GROUP BY A.supplier_id ")
    List<MarketmxDTO> slectSupplierOderUnit(Long foodId, String bgeinDate,String endDate);
    //查询当前提供商品的所有供应商的价格（食材）
    @Select("select B.create_date, A.unit_price As supplierUnitPrice  from canteens_stock_ordermx A ,canteens_stock_order B where A.stock_order_id = B.stock_order_id and A.food_id = '${foodId}' " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') < DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            "and  DATE_FORMAT('${endDate}','%Y-%m-%d') > DATE_FORMAT(B.create_date,'%Y-%m-%d')  ")
    List<MarketmxDTO> slectSupplierOdermxUnit(Long foodId,Long supplierid, String bgeinDate,String endDate);
    //查询当前提供商品的所有供应商的价格（食材）

    @Select("select count(*)as totalNum,A.* from canteens_auxiliary_materialsmx A ,canteens_auxiliary_materi B where A.auxiliary_materials_id = B.auxiliary_materials_id and B.`status`>'4' and A.food_id = '${foodId}' " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') < DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            "and  DATE_FORMAT('${endDate}','%Y-%m-%d') > DATE_FORMAT(B.create_date,'%Y-%m-%d')  GROUP BY A.supplier_id ")
    List<MarketmxDTO> slectSupplierAuxUnit(Long foodId, String bgeinDate,String endDate);
    //查询当前提供商品的所有供应商的价格（食材）
    @Select("select B.create_date, A.unit_price As supplierUnitPrice  from canteens_auxiliary_materialsmx A ,canteens_auxiliary_materi B where A.auxiliary_materials_id = B.auxiliary_materials_id and A.food_id ='${foodId}' " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') < DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            "and  DATE_FORMAT('${endDate}','%Y-%m-%d') > DATE_FORMAT(B.create_date,'%Y-%m-%d')  ")
    List<MarketmxDTO> slectSupplierAuxmxUnit(Long foodId,Long supplierid, String bgeinDate,String endDate);
}